/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import metier.Acteur;
import metier.Autres;
import metier.Film;
import metier.Journaliste;
import metier.ParrainOfficiel;
import metier.Projection;
import metier.Realisateur;
import metier.Salle;
import metier.VIP;

//    public void listStation(Map<String, Station> laMap) throws Exception {
//        Statement stmt = conn.createStatement();
//        ResultSet rs;
//        rs = stmt.executeQuery("select * from STATION");
//        Station st;
//        while (rs.next()) {
//            st = new Station(
//                    rs.getString("numero"),
//                    rs.getString("nom"),
//                    listArrondissement.get(rs.getInt("numarrondissement") - 1),
//                    rs.getString("localisation"));
//            laMap.put(st.getNumero(), st);
//        }
//        rs.close();
//        stmt.close();
//        // conn.close();
//    }
//
//    public void inserer(Station st) throws Exception{
//        
//            String requete = "insert into station VALUES(?,?,?,?)";
//            PreparedStatement pstmt = conn.prepareStatement(requete);
//            pstmt.setString(1, st.getNumero());
//            pstmt.setString(2, st.getNom());
//            pstmt.setInt(3, st.getArrondissement().getNumero());
//            pstmt.setString(4, st.getLocalisation());
//            pstmt.executeUpdate();
//    }
//
//    public void supprimer(String key) throws Exception {
//  
//            String requete = "delete STATION where NUMERO=?";
//            PreparedStatement pstmt = conn.prepareStatement(requete);
//            pstmt.setString(1, key);
//            pstmt.executeUpdate();
//             
//            conn.commit();
//        
//    }
/**
 *
 * @author p1104123
 */
public class DAOCanneOracle {

    private Connection conn;
    private int droit;

    public DAOCanneOracle(Connection c) {
        conn = c;
        droit = 0;
    }

    public void login(String login, String pass) {
        try {
            Statement stat = conn.createStatement();
            ResultSet ensresul = stat.executeQuery(
                    "select * from 	UTILISATEUR where login = '" + login + "' and pass ='" + pass + "'");
            if (ensresul.next()) {
                System.out.println("Login = " + ensresul.getString("LOGIN") + "\nDroit = " + ensresul.getInt("DROIT"));
                droit = ensresul.getInt("DROIT");
            } else {
                droit = 0;
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }

    public void getListVip(List<VIP> la) throws SQLException {
        VIP vip;
        Statement stmt = conn.createStatement();
        ResultSet rs;
        rs = stmt.executeQuery("select numvip from vip order by numvip asc");
        rs.next();
        VIP.setDerniernum(rs.getInt("numvip"));
        Statement stmt1 = conn.createStatement();
        ResultSet rs1;
        rs1 = stmt1.executeQuery("select * from VIP, ACTEUR where VIP.numvip = ACTEUR.numvip order by VIP.numvip asc");

        while (rs1.next()) {
            vip = new Acteur(
                    rs1.getInt("numvip"),
                    rs1.getString("NOM"),
                    rs1.getString("PRENOM"),
                    rs1.getString("NATIONALITE"),
                    rs1.getInt("COEFATTRAIT"),
                    rs1.getString("COMPAGNON"),
                    rs1.getInt("ACCREDITATION"));
            VIP.incrementDerniernum();
            la.add(vip);
        }
        rs1.close();
        stmt1.close();

        Statement stmt2 = conn.createStatement();
        ResultSet rs2;
        rs2 = stmt2.executeQuery("select * from VIP, Journaliste where VIP.numvip = JOURNALISTE.numvip order by VIP.numvip asc");
        VIP vip2;
        while (rs2.next()) {
            vip2 = new Journaliste(
                    rs2.getInt("numvip"),
                    rs2.getString("NOM"),
                    rs2.getString("PRENOM"),
                    rs2.getString("NATIONALITE"),
                    rs2.getInt("COEFATTRAIT"),
                    rs2.getString("COMPAGNON"),
                    rs2.getInt("ACCREDITATION"),
                    rs2.getString("EMPLOYEUR"));
            VIP.incrementDerniernum();
            la.add(vip2);
        }
        rs2.close();
        stmt2.close();

        Statement stmt3 = conn.createStatement();
        ResultSet rs3;

        rs3 = stmt3.executeQuery("select * from VIP, ParrainOfficiel where VIP.numvip = ParrainOfficiel.numvip order by VIP.numvip asc");
        VIP vip3;
        while (rs3.next()) {
            vip3 = new ParrainOfficiel(
                    rs3.getInt("numvip"),
                    rs3.getString("NOM"),
                    rs3.getString("PRENOM"),
                    rs3.getString("NATIONALITE"),
                    rs3.getInt("COEFATTRAIT"),
                    rs3.getString("COMPAGNON"),
                    rs3.getInt("ACCREDITATION"));
            VIP.incrementDerniernum();
            la.add(vip3);
        }
        rs3.close();
        stmt3.close();

        Statement stmt4 = conn.createStatement();
        ResultSet rs4;

        rs4 = stmt4.executeQuery("select * from VIP, REALISATEUR where VIP.numvip = REALISATEUR.numvip order by VIP.numvip asc");
        VIP vip4;
        while (rs4.next()) {
            vip4 = new Realisateur(
                    rs4.getInt("numvip"),
                    rs4.getString("NOM"),
                    rs4.getString("PRENOM"),
                    rs4.getString("NATIONALITE"),
                    rs4.getInt("COEFATTRAIT"),
                    rs4.getString("COMPAGNON"),
                    rs4.getInt("ACCREDITATION"));
            VIP.incrementDerniernum();
            la.add(vip4);
        }

        rs4.close();
        stmt4.close();

        Statement stmt5 = conn.createStatement();
        ResultSet rs5;

        rs5 = stmt5.executeQuery("select * from VIP, AUTRES where VIP.numvip = AUTRES.numvip order by VIP.numvip asc");
        VIP vip5;
        while (rs5.next()) {
            vip5 = new Autres(
                    rs5.getInt("numvip"),
                    rs5.getString("NOM"),
                    rs5.getString("PRENOM"),
                    rs5.getString("NATIONALITE"),
                    rs5.getInt("COEFATTRAIT"),
                    rs5.getString("COMPAGNON"),
                    rs5.getInt("ACCREDITATION"),
                    rs5.getString("METIER"));
            VIP.incrementDerniernum();
            la.add(vip5);
        }
        rs5.close();
        stmt5.close();
        Collections.sort(la);
    }

    public void ajouterDB(VIP vip) {
        try {
            String requete = "insert into VIP VALUES(?,?,?,?,?,?,?)";
            String requete2;
            PreparedStatement pstmt;
            PreparedStatement pstmt2;
            pstmt = conn.prepareStatement(requete);
            pstmt.setInt(1, vip.getNumVIP());
            pstmt.setString(2, vip.getNom());
            pstmt.setString(3, vip.getPrenom());
            pstmt.setString(4, vip.getNationalite());
            pstmt.setInt(5, vip.getCoefAttrait());
            pstmt.setString(6, vip.getCompagnon());
            pstmt.setInt(7, vip.getAccreditation());
            pstmt.executeUpdate();
            pstmt.close();
            
            if (vip instanceof Acteur)
            {
                requete2 = "insert into ACTEUR VALUES(?)";
                pstmt2 = conn.prepareStatement(requete2);
                pstmt2.setInt(1, vip.getNumVIP());
                pstmt2.executeUpdate();
                pstmt2.close();
            }
            
            if (vip instanceof Autres)
            {
                Autres tmp = (Autres) vip; 
                requete2 = "insert into AUTRES VALUES(?, ?)";
                pstmt2 = conn.prepareStatement(requete2);
                pstmt2.setInt(1, tmp.getNumVIP());
                pstmt2.setString(2, tmp.getMetier());
                pstmt2.executeUpdate();
                pstmt2.close();
            }
            
            if (vip instanceof Journaliste)
            {
                Journaliste tmp = (Journaliste) vip;
                requete2 = "insert into Journaliste VALUES(?, ?)";
                pstmt2 = conn.prepareStatement(requete2);
                pstmt2.setInt(1, tmp.getNumVIP());
                pstmt2.setString(2, tmp.getEmployeur());
                pstmt2.executeUpdate();
                pstmt2.close();
            }
            
            if (vip instanceof ParrainOfficiel)
            {
                requete2 = "insert into ParrainOfficiel VALUES(?)";
                pstmt2 = conn.prepareStatement(requete2);
                pstmt2.setInt(1, vip.getNumVIP());
                pstmt2.executeUpdate();
                pstmt2.close();
            }
            
            if (vip instanceof Realisateur)
            {
                requete2 = "insert into Realisateur VALUES(?)";
                pstmt2 = conn.prepareStatement(requete2);
                pstmt2.setInt(1, vip.getNumVIP());
                pstmt2.executeUpdate();
                System.out.println("Ajout réussi");
                pstmt2.close();
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(DAOCanneOracle.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
    
    public void suprVIP(VIP vip)
    {
        try {
            String requete = "delete VIP where numvip=?";
            String requete2;
            PreparedStatement pstmt;
            PreparedStatement pstmt2;
            pstmt = conn.prepareStatement(requete);
            pstmt.setInt(1, vip.getNumVIP());
            
            if (vip instanceof Acteur)
            {
                requete2 = "delete ACTEUR where numvip=?";
                pstmt2 = conn.prepareStatement(requete2);
                pstmt2.setInt(1, vip.getNumVIP());
                pstmt2.executeUpdate();
                pstmt2.close();
                System.out.println("Delete réussi");
            }
            
            if (vip instanceof Autres)
            {
                requete2 = "delete AUTRES where numvip=?";
                pstmt2 = conn.prepareStatement(requete2);
                pstmt2.setInt(1, vip.getNumVIP());
                pstmt2.executeUpdate();
                pstmt2.close();
            }
            
            if (vip instanceof Journaliste)
            {
                requete2 = "delete JOURNALISTE where numvip=?";
                pstmt2 = conn.prepareStatement(requete2);
                pstmt2.setInt(1, vip.getNumVIP());
                pstmt2.executeUpdate();
                pstmt2.close();
            }
            
            if (vip instanceof ParrainOfficiel)
            {
                requete2 = "delete PARRAINOFFICIEL where numvip=?";
                pstmt2 = conn.prepareStatement(requete2);
                pstmt2.setInt(1, vip.getNumVIP());
                pstmt2.executeUpdate();
                pstmt2.close();
            }
            
            if (vip instanceof Realisateur)
            {
                requete2 = "delete REALISATEUR where numvip=?";
                pstmt2 = conn.prepareStatement(requete2);
                pstmt2.setInt(1, vip.getNumVIP());
                pstmt2.executeUpdate();
                pstmt2.close();
            }
         
            pstmt.executeUpdate();
            pstmt.close();
            
            
            
        } catch (SQLException ex) {
            Logger.getLogger(DAOCanneOracle.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void getListProjection(List listProjection) throws SQLException {
        Statement stmt = conn.createStatement();
        ResultSet rs;
        rs = stmt.executeQuery("select * from PROJECTION");

        Projection proj = new Projection();

        while (rs.next()) {
            proj.setNumProjection(rs.getInt("NUMPROJECTION"));
            proj.getFilm().setNumFilm(rs.getInt("NUMFILM"));
            proj.getSalle().setNumSalle(rs.getInt("NUMSALLE"));
            proj.setDate(rs.getString("DATEPROJECTION"));
        }
        listProjection.add(rs);
    }
    
    public void getListProjectionSalle1(List<Projection> lproj) throws SQLException {
        
        Statement stmt = conn.createStatement();
        ResultSet rs;
        rs = stmt.executeQuery("select projection.numfilm, projection.numsalle, dateproj, numprojection, heure_debut, nom, places, titre, duree, categorie from film, projection, salle where projection.numsalle = 1 and projection.numsalle = salle.numsalle and projection.numfilm = film.numfilm");
        Film f = null;
        Salle s = null;
        Projection p = null;
        while (rs.next()) {
            f = new Film(rs.getInt("NUMFILM"), rs.getString("TITRE"), rs.getInt("DUREE"), rs.getString("Categorie"));
        }
                
        
    }

    public int getDroit() {
        return droit;
    }
}
